﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Security.Permissions;
using System.Collections;
using System.Linq;

using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using uacars;

namespace CADLibraryClass
{
    public class CitasCAD
    {
        private string cadenaConexion;

        public CitasCAD()
        {
            cadenaConexion = Singleton.Instancia.Cadena;
        }

        public bool AltaCita(int id, DateTime fecha,DateTime hora, int vendedor, int cliente, string descripcion)
        {
            bool estadoOperacion = false;

            try
            {
                DataSet bdvirtual = new DataSet();

                SqlDataAdapter da = new SqlDataAdapter("select * from citas", cadenaConexion);
                da.Fill(bdvirtual, "citas");

                DataTable dt = new DataTable();
                dt = bdvirtual.Tables["citas"];

                DataRow nuevaFila = dt.NewRow();
                nuevaFila[0] = id;
                nuevaFila[1] = fecha;
                nuevaFila[2] = hora;
                nuevaFila[3] = vendedor;
                nuevaFila[4] = cliente;
                nuevaFila[5] = descripcion;


                dt.Rows.Add(nuevaFila);

                SqlCommandBuilder cBuilder = new SqlCommandBuilder(da);

                da.Update(bdvirtual, "citas");

                dt.AcceptChanges();
            }


            catch (SqlException sqlex)
            {
                throw new Exception("Error insertando pedido en uacarsBD: " + id, sqlex);
            }

            catch (Exception ex)
            {
                throw ex;
            }
            estadoOperacion = true;

            return (estadoOperacion);
        }

        public bool ModificarCita(int id, DateTime fecha, DateTime hora, int vendedor, int cliente, string descripcion)
        {
            bool estadoOperacion;
            try
            {
                DataSet bdvirtual = new DataSet();

                SqlDataAdapter da = new SqlDataAdapter("select * from citas", cadenaConexion);
                da.Fill(bdvirtual, "citas");

                DataTable dt = new DataTable();
                dt = bdvirtual.Tables["citas"];


                DataRow fila = dt.Select("id='" + id + "'")[0];
                fila["id"] = id;
                fila["fecha"] = fecha;
                fila["hora"] = hora;
                fila["vendedor"] = vendedor;
                fila["cliente"] = cliente;
                fila["descripcion"] = descripcion;



                bdvirtual.Tables["citas"].LoadDataRow(fila.ItemArray, true);
                SqlCommandBuilder cBuilder = new SqlCommandBuilder(da);

                da.Update(bdvirtual, "citas");

                dt.AcceptChanges();
            }


            catch (SqlException sqlex)
            {
                throw new Exception("Error modificando la cita en uacarsBD: " + id, sqlex);
            }

            catch (Exception ex)
            {
                throw ex;
            }
            estadoOperacion = true;

            return (estadoOperacion);
        }


        public bool BorrarCita(int id)
        {
            try
            {
                DataSet bdvirtual = new DataSet();

                SqlDataAdapter da = new SqlDataAdapter("select * from citas", cadenaConexion);
                da.Fill(bdvirtual, "citas");

                DataTable dt = new DataTable();
                dt = bdvirtual.Tables["citas"];

                DataRow[] filaBorrar;
                filaBorrar = dt.Select("id = '" + id + "'");
                filaBorrar[0].Delete();

                SqlCommandBuilder cBuilder = new SqlCommandBuilder(da);

                da.Update(bdvirtual, "citas");

                dt.AcceptChanges();

            }

            catch (SqlException sqlex)
            {
                throw new Exception("Error borrando pedido en uacarsBD: " + id, sqlex);
            }

            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }



        public DataTable BuscarCitas()
        {
            DataSet bdvirtual = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter("select * from citas", cadenaConexion);

            da.Fill(bdvirtual, "citas");

            DataTable dt = new DataTable();
            dt = bdvirtual.Tables["citas"];

            return (dt);

        }

        public DataTable BuscarCitas(int id, DateTime fecha, DateTime hora, int vendedor, int cliente, string descripcion)
        {
            string filtro = "";

            if (id != 0) filtro += "id = " + id;
            if (cliente != 0) { if (filtro != "") filtro += " AND "; filtro += "cliente = " + cliente; }
            if (vendedor != 0) { if (filtro != "") filtro += " AND "; filtro += "vendedor = " + cliente; }
            if (descripcion != "null") { if (filtro != "") filtro += " AND "; filtro += "descripcion LIKE '%" + descripcion + "%'"; }
            if (fecha != DateTime.Parse("01/01/1900")) { if (filtro != "") filtro += " AND "; filtro += "fecha = " + "'"+fecha.Date+"'"; }
            if (hora != DateTime.Parse("01/01/1900 0:00:00")) { if (filtro != "") filtro += " AND "; filtro += "hora = " + "'" + fecha.Date + "'"; }
            
            string nombreTabla = "citas";
            DataSet ds = new DataSet();
            ds.Tables.Add(nombreTabla);
            SqlConnection conn = new SqlConnection(cadenaConexion);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand co = new SqlCommand("", conn);
            if (filtro == "")
                co.CommandText = "select * from " + nombreTabla;
            else
                co.CommandText = "select * from " + nombreTabla + " WHERE " + filtro;
            da.SelectCommand = co;
            da.Fill(ds, nombreTabla);

            return ds.Tables[nombreTabla];
        }


        public DataTable DiasMasOcupado()
        {
            string nombreTabla = "citas";
            DataSet ds = new DataSet();
            ds.Tables.Add(nombreTabla);
            SqlConnection conn = new SqlConnection(cadenaConexion);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand co = new SqlCommand("", conn);
            co.CommandText = "select fecha, count(*) total from citas group by fecha having count(*) >= all (select count(*) from citas group by fecha);";
            da.SelectCommand = co;
            da.Fill(ds, nombreTabla);
            return ds.Tables[nombreTabla];       
        }

        public DataTable HoraMasOcupada()
        {
            string nombreTabla = "citas";
            DataSet ds = new DataSet();
            ds.Tables.Add(nombreTabla);
            SqlConnection conn = new SqlConnection(cadenaConexion);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand co = new SqlCommand("", conn);
            co.CommandText = "select hora, count(*) total from citas group by hora having count(*) >= all (select count(*) from citas group by hora);";
            da.SelectCommand = co;
            da.Fill(ds, nombreTabla);
            return ds.Tables[nombreTabla]; 
        }

        public DataTable VendedorConMasCitas()
        {
            string nombreTabla = "citas";
            DataSet ds = new DataSet();
            ds.Tables.Add(nombreTabla);
            SqlConnection conn = new SqlConnection(cadenaConexion);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand co = new SqlCommand("", conn);
            co.CommandText = "select vendedor, count(*) total from citas group by vendedor having count(*) >= all (select count(*) from citas group by vendedor);";
            da.SelectCommand = co;
            da.Fill(ds, nombreTabla);
            return ds.Tables[nombreTabla]; 
        }

        public DataTable ClienteConMasCitas()
        {
            string nombreTabla = "citas";
            DataSet ds = new DataSet();
            ds.Tables.Add(nombreTabla);
            SqlConnection conn = new SqlConnection(cadenaConexion);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand co = new SqlCommand("", conn);
            co.CommandText = "select cliente, count(*) total from citas group by cliente having count(*) >= all (select count(*) from citas group by cliente);";
            da.SelectCommand = co;
            da.Fill(ds, nombreTabla);
            return ds.Tables[nombreTabla];
        }




        public DataTable BuscarCitasIntervalo(int id, DateTime fecha,DateTime fechaFinal, DateTime hora,DateTime horaFinal, int vendedor, int cliente, string descripcion)
        {
            string filtro = "";

            if (id != 0) filtro += "id = " + id;
            if (cliente != 0) { if (filtro != "") filtro += " AND "; filtro += "cliente = " + cliente; }
            if (vendedor != 0) { if (filtro != "") filtro += " AND "; filtro += "vendedor = " + cliente; }
            if (descripcion != "null") { if (filtro != "") filtro += " AND "; filtro += "descripcion LIKE '%" + descripcion + "%'"; }
            if (fecha != DateTime.Parse("01/01/1900") && fechaFinal!= DateTime.Parse("01/01/1900")) { if (filtro != "") filtro += " AND "; filtro += "fecha >= " + "'" + fecha.Date + "' AND fecha <= '"+ fechaFinal.Date +"'"; }
            if (hora != DateTime.Parse("01/01/1900 0:00:00") && horaFinal!= DateTime.Parse("01/01/1900 0:00:00")) { if (filtro != "") filtro += " AND "; filtro += "hora >= " + "'" + hora + "' AND hora <= '"+ horaFinal +"'"; }

            

            string nombreTabla = "citas";
            DataSet ds = new DataSet();
            ds.Tables.Add(nombreTabla);
            SqlConnection conn = new SqlConnection(cadenaConexion);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand co = new SqlCommand("", conn);
            if (filtro == "")
                co.CommandText = "select * from " + nombreTabla;
            else
                co.CommandText = "select * from " + nombreTabla + " WHERE " + filtro;
            da.SelectCommand = co;
            da.Fill(ds, nombreTabla);

            return ds.Tables[nombreTabla];
        }


  
    }
}


